/**
 * 
 */
package sql;

import java.awt.Color;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

import dao.Client;
import dao.Dungeon;
import dao.Monster;
import dao.Room;
import dao.UserCharacter;

/**
 * @author cGuiraud
 *
 */
public class CallRawDB implements DB_Interface {
	
	// ROOMS

	/* (non-Javadoc)
	 * @see sql.DB_Interface#createRoom(dao.Room)
	 */
	@Override
	public void createRoom(Room r) throws Exception {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			stmt.executeUpdate("INSERT INTO room(idroom) " + "VALUES("
					+ r.getIdroom() + ")");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public void createAllRooms(List<Room> roomlist) throws Exception {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			for (Room room : roomlist) {
				stmt = (Statement) con.createStatement();
				String req = "INSERT INTO room(idroom) " + "VALUES("
						+ room.getIdroom() + ")";
				stmt.executeUpdate(req);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see sql.DB_Interface#deleteRoom(dao.Room)
	 */
	@Override
	public void deleteRoom(Room r) throws Exception {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "DELETE FROM room WHERE idroom="+r.idRoom;
			stmt.executeUpdate(req);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

	/* (non-Javadoc)
	 * @see sql.DB_Interface#getAllRooms()
	 */
	@Override
	public List<Room> getAllRooms(Dungeon myDungeon) throws Exception {
		Room r = null;
		List<Room> res = new ArrayList<Room>();
		try {
			ResultSet rs;
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "SELECT * FROM room";
			rs = stmt.executeQuery(req);
			while (rs.next()) {
				r = new Room(rs.getInt("idroom"));
				r.setMyDungeon(myDungeon);
				res.add(r);
            }
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return res;
	}

	@Override
	public void clearRoom() throws Exception {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "DELETE FROM room";
			stmt.executeUpdate(req);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	// MONSTERS

	/* (non-Javadoc)
	 * @see sql.DB_Interface#createMonster(dao.Monster)
	 */
	@Override
	public void createMonster(Monster m) throws Exception {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			stmt.executeUpdate("INSERT INTO monster(life, damage, armor, nameMonster, idRoomMonster) "
			+ "VALUES("+m.life+","+m.damage+","+m.armor+",'"+m.nameMonster+"',"+m.getCurrentRoom().getIdroom()+")");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public void createAllMonsters(List<Monster> monsterlist) throws Exception {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			for (Monster m : monsterlist) {
				stmt = (Statement) con.createStatement();
				String req = "INSERT INTO monster(life, damage, armor, nameMonster, idRoomMonster) "
						+ "VALUES("
						+ m.life
						+ ","
						+ m.damage
						+ ","
						+ m.armor
						+ ",'"
						+ m.nameMonster
						+ "',"
						+ m.getCurrentRoom().getIdroom() + ")";
				stmt.executeUpdate(req);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see sql.DB_Interface#updateMonster(dao.Monster)
	 */
	@Override
	public void updateMonster(Monster m) throws Exception {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "UPDATE monster SET "
						+"  life="+m.getLife()
						+", damage="+m.getDamage()
						+", armor="+m.getArmor()
						+", nameMonster='"+m.getNameMonster()+"'"
						+", idRoomMonster="+m.getCurrentRoom().getIdroom()
						+" WHERE idMonster="+m.getIdMonster();
			stmt.executeUpdate(req);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see sql.DB_Interface#deleteMonster(dao.Monster)
	 */
	@Override
	public void deleteMonster(Monster m) throws Exception {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "DELETE FROM monster WHERE idMonster="+m.getIdMonster();
			stmt.executeUpdate(req);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see sql.DB_Interface#getAllMonsters()
	 */
	@Override
	public List<Monster> getAllMonsters() throws Exception {
		Monster m = null;
		List<Monster> res = new ArrayList<Monster>();
		try {
			ResultSet rs;
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "SELECT * FROM monster";
			rs = stmt.executeQuery(req);
			while (rs.next()) {
				m = new Monster();
				m.setIdMonster(rs.getInt("idMonster"));
				m.setLife(rs.getInt("life"));
				m.setDamage(rs.getInt("damage"));
				m.setArmor(rs.getInt("armor"));
				m.setNameMonster(rs.getString("nameMonster"));
				m.setCurrentRoom(new Room(rs.getInt("idRoomMonster")));
				res.add(m);
            }
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return res;
	}
	
	public List<Monster> getAllMonster(List<Room> rooms) {
		Monster m = null;
		HashMap<Integer, Room> hm_rooms = new HashMap<Integer, Room>(); 
		for (Room r : rooms) {
			hm_rooms.put(r.getIdroom(), r);
		}
		List<Monster> res = new ArrayList<Monster>();
		try {
			ResultSet rs;
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "SELECT * FROM monster";
			rs = stmt.executeQuery(req);
			while (rs.next()) {
				m = new Monster();
				m.setIdMonster(rs.getInt("idMonster"));
				m.setLife(rs.getInt("life"));
				m.setDamage(rs.getInt("damage"));
				m.setArmor(rs.getInt("armor"));
				m.setNameMonster(rs.getString("nameMonster"));
				m.setCurrentRoom(hm_rooms.get(rs.getInt("idRoomMonster")));
				hm_rooms.get(rs.getInt("idRoomMonster")).monster = m;
				res.add(m);
            }
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return res;
	}

	/* (non-Javadoc)
	 * @see sql.DB_Interface#getMonster(int)
	 */
	@Override
	public Monster getMonster(int idmonster) throws Exception {
		Monster m = null;
		try {
			ResultSet rs;
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "SELECT * FROM monster";
			rs = stmt.executeQuery(req);
			if (rs.next()) {
				m = new Monster();
				m.setIdMonster(rs.getInt("idMonster"));
				m.setLife(rs.getInt("life"));
				m.setDamage(rs.getInt("damage"));
				m.setArmor(rs.getInt("armor"));
				m.setNameMonster(rs.getString("nameMonster"));
				m.setCurrentRoom(new Room(rs.getInt("idRoomMonster")));
            }
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return m;
	}
	
	public Monster getMonster(int idmonster, List<Room> rooms) throws Exception {
		Monster m = null;
		HashMap<Integer, Room> hm_rooms = new HashMap<Integer, Room>(); 
		for (Room r : rooms) {
			hm_rooms.put(r.getIdroom(), r);
		}
		try {
			ResultSet rs;
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "SELECT * FROM monster";
			rs = stmt.executeQuery(req);
			if (rs.next()) {
				m = new Monster();
				m.setIdMonster(rs.getInt("idMonster"));
				m.setLife(rs.getInt("life"));
				m.setDamage(rs.getInt("damage"));
				m.setArmor(rs.getInt("armor"));
				m.setNameMonster(rs.getString("nameMonster"));
				m.setCurrentRoom(hm_rooms.get(rs.getInt("idRoomMonster")));
            }
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return m;
	}

	@Override
	public void clearMonster() throws Exception {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "DELETE FROM monster";
			stmt.executeUpdate(req);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	//USERCHARACTER

	/* (non-Javadoc)
	 * @see sql.DB_Interface#createUserCharacter(dao.UserCharacter)
	 */
	@Override
	public void createUserCharacter(UserCharacter uc) throws Exception {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();

			stmt.executeUpdate("INSERT INTO usercharacter(pseudo, life, lifemax, damage, armor, peau, habits, yeux, cheveux, sexe, currentRoom, logincli) "
					+ "VALUES('"+uc.getPseudo()+"',"+uc.getLife()+","+uc.getLife_max()+","+uc.getDamage()+","+uc.getArmor()
					+","+uc.getPeau().getRGB()
					+","+uc.getHabits().getRGB()
					+","+uc.getYeux().getRGB()
					+","+uc.getCheveux().getRGB()
					+",'"+uc.getSexe()+"'"
					+","+uc.getCurrentRoom().getIdroom()
					+",'"+uc.getOwner().getLogin()+"')");
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public void createAllUserCharacters(List<UserCharacter> uclist)
			throws Exception {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			for (UserCharacter uc : uclist) {
				stmt = (Statement) con.createStatement();
				String str = "INSERT INTO usercharacter(pseudo, life, lifemax, damage, armor, peau, habits, yeux, cheveux, sexe, currentRoom, logincli) "
						+ "VALUES('"
						+ uc.getPseudo()
						+ "',"
						+ uc.getLife()
						+ ","
						+ uc.getLife_max()
						+ ","
						+ uc.getDamage()
						+ ","
						+ uc.getArmor()
						+ ","
						+ uc.getPeau().getRGB()
						+ ","
						+ uc.getHabits().getRGB()
						+ ","
						+ uc.getYeux().getRGB()
						+ ","
						+ uc.getCheveux().getRGB()
						+ ",'"
						+ uc.getSexe()
						+ "'"
						+ ","
						+ uc.getCurrentRoom().getIdroom()
						+ ",'"
						+ uc.getOwner().getLogin() + "')";
				stmt.executeUpdate(str);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}


	/* (non-Javadoc)
	 * @see sql.DB_Interface#updateUserCharacter(dao.UserCharacter)
	 */
	@Override
	public void updateUserCharacter(UserCharacter uc) throws Exception {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "UPDATE usercharacter SET "
						+"life="+uc.getLife()
						+", lifemax="+uc.getLife_max()
						+", damage="+uc.getDamage()
						+", armor="+uc.getArmor()
						+", peau="+uc.getPeau().getRGB()
						+", habits="+uc.getHabits().getRGB()
						+", yeux="+uc.getYeux().getRGB()
						+", cheveux="+uc.getCheveux().getRGB()
						+", sexe='"+uc.getSexe()+"'"
						+", currentRoom="+uc.getCurrentRoom().getIdroom()
						+", logincli='"+uc.getOwner().getLogin()+"'"
						+" WHERE pseudo='"+uc.getPseudo()+"'";
			stmt.executeUpdate(req);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see sql.DB_Interface#deleteUserCharacter(dao.UserCharacter)
	 */
	@Override
	public void deleteUserCharacter(UserCharacter uc) throws Exception {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "DELETE FROM usercharacter WHERE pseudo='"+uc.getPseudo()+"'";
			stmt.executeUpdate(req);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see sql.DB_Interface#getAllUserCharacters()
	 */
	@Override
	public List<UserCharacter> getAllUserCharacters() throws Exception {
		UserCharacter uc = null;
		List<UserCharacter> res = new ArrayList<UserCharacter>();
		try {
			ResultSet rs;
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "SELECT * FROM usercharacter";
			rs = stmt.executeQuery(req);
			while (rs.next()) {
				uc = new UserCharacter();
				uc.setLife(rs.getInt("life"));
				uc.setLife_max(rs.getInt("lifemax"));
				uc.setDamage(rs.getInt("damage"));
				uc.setArmor(rs.getInt("armor"));
				uc.setCheveux(new Color(rs.getInt("cheveux")));
				uc.setHabits(new Color(rs.getInt("habits")));
				uc.setYeux(new Color(rs.getInt("yeux")));
				uc.setPeau(new Color(rs.getInt("peau")));
				uc.setCurrentRoom(new Room(rs.getInt("currentRoom")));
				uc.setOwner(new Client(rs.getString("logincli"),"",""));
				res.add(uc);
            }
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return res;
	}
	
	public List<UserCharacter> getAllUserCharacters(List<Room> rooms, List<Client> clients) throws Exception {
		UserCharacter uc = null;
		List<UserCharacter> res = new ArrayList<UserCharacter>();
		HashMap<Integer, Room> hm_rooms = new HashMap<Integer, Room>(); 
		for (Room r : rooms) {
			hm_rooms.put(r.getIdroom(), r);
		}
		HashMap<String, Client> hm_clients = new HashMap<String, Client>(); 
		for (Client c : clients) {
			hm_clients.put(c.login, c);
		}
		try {
			ResultSet rs;
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "SELECT * FROM usercharacter";
			rs = stmt.executeQuery(req);
			while (rs.next()) {
				uc = new UserCharacter();
				uc.setPseudo(rs.getString("pseudo"));
				uc.setLife(rs.getInt("life"));
				uc.setLife_max(rs.getInt("lifemax"));
				uc.setDamage(rs.getInt("damage"));
				uc.setArmor(rs.getInt("armor"));
				uc.setCheveux(new Color(rs.getInt("cheveux")));
				uc.setHabits(new Color(rs.getInt("habits")));
				uc.setYeux(new Color(rs.getInt("yeux")));
				uc.setPeau(new Color(rs.getInt("peau")));
				uc.setSexe(rs.getString("sexe"));
				Room r = hm_rooms.get(rs.getInt("currentRoom"));
				uc.setCurrentRoom(r);
				Client cli = hm_clients.get(rs.getString("logincli"));
				uc.setOwner(cli);
				res.add(uc);
            }
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return res;
	}

	/* (non-Javadoc)
	 * @see sql.DB_Interface#getUserCharacter(int)
	 */
	@Override
	public UserCharacter getUserCharacter(String pseudouc) throws Exception {
		UserCharacter uc = null;
		try {
			ResultSet rs;
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "SELECT * FROM usercharacter WHERE pseudo='"+pseudouc+"'";
			rs = stmt.executeQuery(req);
			if (rs.next()) {
				uc = new UserCharacter();
				uc.setLife(rs.getInt("life"));
				uc.setLife_max(rs.getInt("lifemax"));
				uc.setDamage(rs.getInt("damage"));
				uc.setArmor(rs.getInt("armor"));
				uc.setCheveux(new Color(rs.getInt("cheveux")));
				uc.setHabits(new Color(rs.getInt("habits")));
				uc.setYeux(new Color(rs.getInt("yeux")));
				uc.setPeau(new Color(rs.getInt("peau")));
				uc.setCurrentRoom(new Room(rs.getInt("currentRoom")));
				uc.setOwner(new Client(rs.getString("logincli"),"",""));
            }
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return uc;
	}
	
	public UserCharacter getUserCharacter(int iduc, List<Room> rooms, List<Client> clients) throws Exception {
		UserCharacter uc = null;
		HashMap<Integer, Room> hm_rooms = new HashMap<Integer, Room>(); 
		for (Room r : rooms) {
			hm_rooms.put(r.getIdroom(), r);
		}
		HashMap<String, Client> hm_clients = new HashMap<String, Client>(); 
		for (Client c : clients) {
			hm_clients.put(c.login, c);
		}
		try {
			ResultSet rs;
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "SELECT * FROM usercharacter";
			rs = stmt.executeQuery(req);
			if (rs.next()) {
				uc = new UserCharacter();
				uc.setLife(rs.getInt("life"));
				uc.setLife_max(rs.getInt("lifemax"));
				uc.setDamage(rs.getInt("damage"));
				uc.setArmor(rs.getInt("armor"));
				uc.setCheveux(new Color(rs.getInt("cheveux")));
				uc.setHabits(new Color(rs.getInt("habits")));
				uc.setYeux(new Color(rs.getInt("yeux")));
				uc.setPeau(new Color(rs.getInt("peau")));
				Room r = hm_rooms.get(rs.getInt("currentRoom"));
				uc.setCurrentRoom(r);
				Client cli = hm_clients.get(rs.getString("logincli"));
				uc.setOwner(cli);
            }
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return uc;
	}
	
	@Override
	public void clearUserCharacter() throws Exception {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "DELETE FROM usercharacter";
			stmt.executeUpdate(req);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	
	
	// CLIENTS
	
	/* (non-Javadoc)
	 * @see sql.DB_Interface#createClient(dao.Client)
	 */
	@Override
	public void createClient(Client c) throws Exception {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			stmt.executeUpdate("INSERT INTO client(login, password, email) "
			+ "VALUES('"+c.login+"','"+c.password+"','"+c.email+"')");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public void createAllClients(List<Client> clientlist) throws Exception {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			for (Client cli : clientlist) {
				stmt = (Statement) con.createStatement();
				String req = "INSERT INTO client(login, password, email) "
						+ "VALUES('" + cli.login + "','" + cli.password + "','"
						+ cli.email + "')";
				stmt.executeUpdate(req);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see sql.DB_Interface#updateClient(dao.Client)
	 */
	@Override
	public void updateClient(Client c) throws Exception {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "UPDATE client SET "
						+"  password='"+c.getPassword()+"'"
						+", email='"+c.getEmail()+"'"
						+" WHERE login='"+c.getLogin()+"'";
			stmt.executeUpdate(req);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see sql.DB_Interface#deleteClient(dao.Client)
	 */
	@Override
	public void deleteClient(Client c) throws Exception {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "DELETE FROM client WHERE login='"+c.login+"'";
			stmt.executeUpdate(req);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see sql.DB_Interface#getAllClients()
	 */
	@Override
	public List<Client> getAllClients() throws Exception {
		Client c = null;
		List<Client> res = new ArrayList<Client>();
		try {
			ResultSet rs;
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "SELECT * FROM client";
			rs = stmt.executeQuery(req);
			while (rs.next()) {
				c = new Client(rs.getString("login"),rs.getString("password"),rs.getString("email"));
				res.add(c);
            }
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return res;
	}

	/* (non-Javadoc)
	 * @see sql.DB_Interface#getClient(int)
	 */
	public Client getClient(String logincli) throws Exception {
		Client c = null;
		try {
			ResultSet rs;
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "SELECT * FROM client WHERE login='"+logincli+"'";
			rs = stmt.executeQuery(req);
			if (rs.next()) {
				c = new Client(rs.getString("login"),rs.getString("password"),rs.getString("email"));
            }
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return c;
	}

	@Override
	@Deprecated
	public void updateRoom(Room r) throws Exception {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void clearClient() throws Exception {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "DELETE FROM client";
			stmt.executeUpdate(req);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	// LINKROOMS

	@Override
	public void createAllLinks(List<Room> listroom) throws Exception {
		
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
		int r1, r2;
		String dest;
		for (Room room : listroom) {
			r1 = room.idRoom;
			for (String destin : room.to.keySet()) {
				dest = destin;
				r2 = room.to.get(destin).getIdroom();
				stmt = (Statement) con.createStatement();
				String req = "INSERT INTO linkroom(room1, room2, destination) "
						+ "VALUES("+r1+","+r2+"','"+dest+"')";
				stmt.executeUpdate(req);
			}
		}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public void setAllLinks(HashMap<Integer, Room> hmrooms) throws Exception {
		try {
			ResultSet rs;
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "SELECT * FROM linkroom";
			rs = stmt.executeQuery(req);
			int r1, r2;
			String destination;
			while (rs.next()) {
				r1 = rs.getInt("room1");
				r2 = rs.getInt("room2");
				destination = rs.getString("destination");
				if (hmrooms.get(r1) != null && hmrooms.get(r2) != null) {
					if (destination.equals("N") || destination.equals("S") || destination.equals("O") || destination.equals("E")) {
						hmrooms.get(r1).to.put(destination, hmrooms.get(r2));
					}
				}
            }
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void clearLinkRooms() throws Exception {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/bd_rmi";
			Connection con = (Connection) DriverManager.getConnection(url,
					"root", "");
			stmt = (Statement) con.createStatement();
			String req = "DELETE FROM linkroom";
			stmt.executeUpdate(req);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public Room getRoom(int idroom) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

}
